with operations_trace_basic as (
select
waybill_no
,step
,scan_type
,network_code
,network_type
,network_name
,network_scan_time_early
,scan_time_early
,scan_time_last
,step_center
,step_center_reserve
,step_entrepot
,step_entrepot_reserve
,named_struct(
'scan_time_early', scan_time_early,
'scan_time_last', scan_time_last,
'network_code', network_code,
'network_type', network_type,
'network_name', network_name,
'prev_network_code', prev_network_code,
'prev_network_type', prev_network_type,
'next_network_code', next_network_code,
'next_network_type', next_network_type,
'scan_type', scan_type,
'packagecode', packagecode,
'nextstationcode', nextstationcode,
'nextstation', nextstation,
'scanuser', scanuser,
'weight', weight,
'shipment_no', shipment_no,
'planned_arrival_time', planned_arrival_time,
'actual_arrival_time', actual_arrival_time,
'planned_departure_time', planned_departure_time,
'actual_departure_time', actual_departure_time
) as trace_wid
from jms_dwd.dwd_s01_whole_operations_trace_dt where dt between date_add('{{ execution_date | cst_ds }}', -15) and '{{ execution_date | cst_ds }}' ) ,

waybill_and_step as (
select
waybill_no
,min(if(step = 1,network_scan_time_early,null)) as waybill_first_scantime      --最早操作时间
,min(if(step = 1,trace_wid,null)).scan_type as waybill_first_scantype          --最早扫描类型
,max(scan_time_last) as waybill_end_scantime                                   --最晚操作时间
,max(trace_wid).scan_type as waybill_end_scantype                              --最晚扫描类型
,count(1)              as        waybill_all_operation_times                   --运单扫描总次数
,sum(if(network_type = '2',1,0))  as  center_all_operation_times               --中心扫描总次数
,sum(if(network_type = '3',1,0))  as  nodal_all_operation_times                --集散网点扫描次数
,max(step)             as        network_all_count                             --运单总经过网点数
,max(step_center)      as        center_all_count                              --运单总经过中心数
,max(step_entrepot)    as        nodal_all_count                               --运单总经过集散网点数

,min(if(scan_type = 'taking' or scan_type ='warehousing', scan_time_early ,null)) as actual_taking_scantime        --实际揽收时间
,min(if(scan_type = 'taking' or scan_type ='warehousing', network_code ,null)) as actual_taking_scansitecode       --实际揽收网点
,min(if(scan_type = 'taking' or scan_type ='warehousing', trace_wid ,null)).weight as actual_taking_weight         --实际揽收重量
,min(if(scan_type = 'taking' or scan_type ='warehousing', trace_wid ,null)).scan_type as actual_taking_scantype    --实际揽收扫描类型

,max(if(scan_type = 'taking' , scan_time_last ,null)) as taking_later_scantime                                     --最晚揽收时间
,min(if(scan_type = 'taking' , scan_time_early ,null)) as taking_early_scantime                                    --最早揽收时间
,min(if(scan_type = 'taking' , trace_wid ,null)).network_code as taking_early_scansitecode                         --最早揽收网点
,min(if(scan_type = 'taking' , trace_wid ,null)).weight as taking_early_weight                                     --最早揽收重量

,max(if(scan_type = 'warehousing' , scan_time_last ,null)) as warehousing_later_scantime                           --最晚入仓时间
,min(if(scan_type = 'warehousing' , scan_time_early ,null)) as warehousing_early_scantime                          --最早入仓时间
,min(if(scan_type = 'warehousing' , trace_wid ,null)).network_code as warehousing_early_scansitecode               --最早入仓网点
,min(if(scan_type = 'warehousing' , trace_wid ,null)).weight as warehousing_early_weight                           --最早入仓重量

,min(if(scan_type = 'bagging' , scan_time_early ,null)) as bagging_early_scantime                                  --最早装袋时间
,min(if(scan_type = 'bagging' , trace_wid ,null)).network_code as bagging_early_scansitecode                       --最早装袋网点编码
,min(if(scan_type = 'bagging' , trace_wid ,null)).packagecode as bagging_early_package_code                        --最早装袋包号

,min(if(step = 1,network_code,null)) as first_network_network_code                                                 --始发网点网点编码
,max(if(step = 1 and scan_type = 'loading', scan_time_last,null))  as first_network_loading_later_time             --始发网点最晚装车时间
,max(if(step = 1 and scan_type = 'loading', trace_wid, null)).weight  as first_network_loading_later_weight        --始发网点最晚装车扫描对应重量
,max(if(step = 1 and (scan_type = 'centerSend' or scan_type = 'siteSend'), scan_time_last, null))  as first_network_send_later_time            --始发网点最晚发车时间
,max(if(step = 1 and (scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).weight  as first_network_send_later_weight        --始发网点最晚发车扫描对应重量
--,max(if(step = 1 and (scan_type = 'loading' or scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).shipment_no  as first_network_loading_send_shipment_no                    --始发网点最晚装车发件扫描对应任务号
--,max(if(step = 1 and (scan_type = 'loading' or scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).planned_departure_time  as first_network_loading_send_planned_departure_time         --始发网点最晚装车发件规划发车时间
--,max(if(step = 1 and (scan_type = 'loading' or scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).planned_arrival_time  as first_network_loading_send_planned_arrival_time           --始发网点最晚装车发件规划到车时间
--,max(if(step = 1 and (scan_type = 'loading' or scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).actual_departure_time  as first_network_loading_send_actual_departure_time          --始发网点最晚装车发件实际发车时间
--,max(if(step = 1 and (scan_type = 'loading' or scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).actual_arrival_time  as first_network_loading_send_actual_arrival_time            --始发网点最晚装车发件实际到车时间
,coalesce(max(if(step = 1 and  scan_type = 'loading', trace_wid, null)).shipment_no,max(if(step = 1 and (scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).shipment_no) as first_network_loading_send_shipment_no
,coalesce(max(if(step = 1 and  scan_type = 'loading', trace_wid, null)).planned_departure_time,max(if(step = 1 and (scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).planned_departure_time)  as first_network_loading_send_planned_departure_time
,coalesce(max(if(step = 1 and  scan_type = 'loading', trace_wid, null)).planned_arrival_time,max(if(step = 1 and (scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).planned_arrival_time)  as first_network_loading_send_planned_arrival_time
,coalesce(max(if(step = 1 and  scan_type = 'loading', trace_wid, null)).actual_departure_time,max(if(step = 1 and (scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).actual_departure_time) as first_network_loading_send_actual_departure_time
,coalesce(max(if(step = 1 and  scan_type = 'loading', trace_wid, null)).actual_arrival_time,max(if(step = 1 and (scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).actual_arrival_time) as first_network_loading_send_actual_arrival_time
,max(if(step = 1 and (scan_type = 'loading' or scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).next_network_code  as first_network_loading_send_next_network_code         --始发网点最晚装车发件下一站网点编码
,max(if(step = 1 and (scan_type = 'loading' or scan_type = 'centerSend' or scan_type = 'siteSend'), trace_wid, null)).next_network_type  as first_network_loading_send_next_network_type         --始发网点最晚装车发件下一站网点类型

,min(if(step_entrepot = 1 ,network_code,null))  as first_nodal_network_code                                                               -- 始发集散网点
,min(if(step_entrepot = 1 and scan_type = 'unloading', scan_time_last,null))   as first_nodal_unloading_early_time                         -- 始发集散最早卸车时间
,min(if(step_entrepot = 1 and scan_type = 'unloading', trace_wid,null)).weight   as first_nodal_unloading_early_weight                            -- 始发集散最早卸车扫描对应重量
,min(if(step_entrepot = 1 and scan_type = 'siteArrival', scan_time_early,null))   as first_nodal_arrival_early_time    -- 始发集散最早到件时间
,min(if(step_entrepot = 1 and scan_type = 'siteArrival', trace_wid,null)).weight   as first_nodal_arrival_early_weight  -- 始发集散最早到件扫描对应重量
--,min(if(step_entrepot = 1 and (scan_type = 'unloading' or scan_type = 'siteArrival'), trace_wid,null)).shipment_no as first_nodal_unloading_arrival_shipment_no              -- 始发集散最早卸车到件扫描对应任务号
--,min(if(step_entrepot = 1 and (scan_type = 'unloading' or scan_type = 'siteArrival'), trace_wid,null)).planned_departure_time as first_nodal_unloading_arrival_planned_departure_time   -- 始发集散最早卸车到件规划发车时间
--,min(if(step_entrepot = 1 and (scan_type = 'unloading' or scan_type = 'siteArrival'), trace_wid,null)).planned_arrival_time as first_nodal_unloading_arrival_planned_arrival_time     -- 始发集散最早卸车到件规划到车时间
--,min(if(step_entrepot = 1 and (scan_type = 'unloading' or scan_type = 'siteArrival'), trace_wid,null)).actual_departure_time as first_nodal_unloading_arrival_actual_departure_time    -- 始发集散最早卸车到件实际发车时间
--,min(if(step_entrepot = 1 and (scan_type = 'unloading' or scan_type = 'siteArrival'), trace_wid,null)).actual_arrival_time as first_nodal_unloading_arrival_actual_arrival_time      -- 始发集散最早卸车到件实际到车时间
,coalesce(min(if(step_entrepot = 1 and scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_entrepot = 1 and scan_type = 'siteArrival', trace_wid,null)).shipment_no) as first_nodal_unloading_arrival_shipment_no
,coalesce(min(if(step_entrepot = 1 and scan_type = 'unloading', trace_wid,null)).planned_departure_time,min(if(step_entrepot = 1 and scan_type = 'siteArrival', trace_wid,null)).planned_departure_time) as first_nodal_unloading_arrival_planned_departure_time
,coalesce(min(if(step_entrepot = 1 and scan_type = 'unloading', trace_wid,null)).planned_arrival_time,min(if(step_entrepot = 1 and scan_type = 'siteArrival', trace_wid,null)).planned_arrival_time) as first_nodal_unloading_arrival_planned_arrival_time
,coalesce(min(if(step_entrepot = 1 and scan_type = 'unloading', trace_wid,null)).actual_departure_time,min(if(step_entrepot = 1 and scan_type = 'siteArrival', trace_wid,null)).actual_departure_time) as first_nodal_unloading_arrival_actual_departure_time
,coalesce(min(if(step_entrepot = 1 and scan_type = 'unloading', trace_wid,null)).actual_arrival_time,min(if(step_entrepot = 1 and scan_type = 'siteArrival', trace_wid,null)).actual_arrival_time) as first_nodal_unloading_arrival_actual_arrival_time
,max(if(step_entrepot = 1 and scan_type = 'loading', scan_time_last,null)) as first_nodal_loading_later_time                 -- 始发集散最晚装车时间
,max(if(step_entrepot = 1 and scan_type = 'loading', trace_wid,null)).weight as first_nodal_loading_later_weight                 -- 始发集散最晚装车扫描对应重量
,max(if(step_entrepot = 1 and scan_type = 'siteSend' , scan_time_last,null)) as first_nodal_send_later_time  -- 始发集散最晚发车时间
,max(if(step_entrepot = 1 and scan_type = 'siteSend' , trace_wid,null)).weight as first_nodal_send_later_weight   -- 始发集散最晚发车扫描对应重量
--,max(if(step_entrepot = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).shipment_no as first_nodal_loading_send_shipment_no                        -- 始发集散最晚装车发件扫描对应任务号
--,max(if(step_entrepot = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).planned_departure_time as first_nodal_loading_send_planned_departure_time  -- 始发集散最晚装车发件规划发车时间
--,max(if(step_entrepot = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).planned_arrival_time as first_nodal_loading_send_planned_arrival_time    -- 始发集散最晚装车发件规划到车时间
--,max(if(step_entrepot = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).actual_departure_time as first_nodal_loading_send_actual_departure_time   -- 始发集散最晚装车发件实际发车时间
--,max(if(step_entrepot = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).actual_arrival_time as first_nodal_loading_send_actual_arrival_time     -- 始发集散最晚装车发件实际到车时间
,coalesce(max(if(step_entrepot = 1 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_entrepot = 1 and scan_type = 'siteSend', trace_wid,null)).shipment_no) as first_nodal_loading_send_shipment_no
,coalesce(max(if(step_entrepot = 1 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_entrepot = 1 and scan_type = 'siteSend', trace_wid,null)).planned_departure_time) as first_nodal_loading_send_planned_departure_time
,coalesce(max(if(step_entrepot = 1 and scan_type = 'loading', trace_wid,null)).planned_arrival_time, max(if(step_entrepot = 1 and scan_type = 'siteSend', trace_wid,null)).planned_arrival_time) as first_nodal_loading_send_planned_arrival_time
,coalesce(max(if(step_entrepot = 1 and scan_type = 'loading', trace_wid,null)).actual_departure_time, max(if(step_entrepot = 1 and scan_type = 'siteSend', trace_wid,null)).actual_departure_time) as first_nodal_loading_send_actual_departure_time
,coalesce(max(if(step_entrepot = 1 and scan_type = 'loading', trace_wid,null)).actual_arrival_time, max(if(step_entrepot = 1 and scan_type = 'siteSend', trace_wid,null)).actual_arrival_time) as first_nodal_loading_send_actual_arrival_time
,max(if(step_entrepot = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).next_network_code as first_nodal_loading_send_next_network_code       -- 始发集散最晚装车发件下一站网点编码
,max(if(step_entrepot = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).next_network_type as first_nodal_loading_send_next_network_type       -- 始发集散最晚装车发件下一站网点类型

,max(if(step_center = 1, network_code,null)) as first_center_network_code                                                -- 始发转运中心
,min(if(step_center = 1 and scan_type = 'unloading', scan_time_early,null))  as first_center_unloading_early_time        -- 始发中心最早卸车时间
,min(if(step_center = 1 and scan_type = 'unloading', trace_wid,null)).weight  as first_center_unloading_early_weight     -- 始发中心最早卸车扫描对应重量
,min(if(step_center = 1 and scan_type = 'centerArrival', scan_time_early,null)) as first_center_arrival_early_time       -- 始发中心最早到件时间
,min(if(step_center = 1 and scan_type = 'centerArrival', trace_wid,null)).weight as first_center_arrival_early_weight    -- 始发中心最早到件扫描对应重量
--,min(if(step_center = 1 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).shipment_no as first_center_unloading_arrival_shipment_no               -- 始发中心最早卸车到件扫描对应任务号
--,min(if(step_center = 1 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).planned_departure_time as first_center_unloading_arrival_planned_departure_time   -- 始发中心最早卸车到件规划发车时间
--,min(if(step_center = 1 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).planned_arrival_time as first_center_unloading_arrival_planned_arrival_time     -- 始发中心最早卸车到件规划到车时间
--,min(if(step_center = 1 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).actual_departure_time as first_center_unloading_arrival_actual_departure_time    -- 始发中心最早卸车到件实际发车时间
--,min(if(step_center = 1 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).actual_arrival_time as first_center_unloading_arrival_actual_arrival_time      -- 始发中心最早卸车到件实际到车时间
,coalesce(min(if(step_center = 1 and  scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_center = 1 and scan_type = 'centerArrival', trace_wid,null)).shipment_no) as first_center_unloading_arrival_shipment_no
,coalesce(min(if(step_center = 1 and  scan_type = 'unloading', trace_wid,null)).planned_departure_time, min(if(step_center = 1 and scan_type = 'centerArrival', trace_wid,null)).planned_departure_time) as first_center_unloading_arrival_planned_departure_time
,coalesce(min(if(step_center = 1 and  scan_type = 'unloading', trace_wid,null)).planned_arrival_time, min(if(step_center = 1 and scan_type = 'centerArrival', trace_wid,null)).planned_arrival_time) as first_center_unloading_arrival_planned_arrival_time
,coalesce(min(if(step_center = 1 and  scan_type = 'unloading', trace_wid,null)).actual_departure_time, min(if(step_center = 1 and scan_type = 'centerArrival', trace_wid,null)).actual_departure_time) as first_center_unloading_arrival_actual_departure_time
,coalesce(min(if(step_center = 1 and  scan_type = 'unloading', trace_wid,null)).actual_arrival_time, min(if(step_center = 1 and scan_type = 'centerArrival', trace_wid,null)).actual_arrival_time) as first_center_unloading_arrival_actual_arrival_time
,max(if(step_center = 1 and scan_type = 'loading', scan_time_last,null)) as first_center_loading_later_time      -- 始发中心最晚装车时间
,max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).weight as first_center_loading_later_weight  -- 始发中心最晚装车扫描对应重量
,max(if(step_center = 1 and scan_type = 'centerSend', scan_time_last,null)) as first_center_send_later_time      -- 始发中心最晚发件时间
,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).weight as first_center_send_later_weight  -- 始发中心最晚发件扫描对应重量
--,max(if(step_center = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).shipment_no as first_center_loading_send_shipment_no                              -- 始发中心最晚装车发件扫描对应任务号
--,max(if(step_center = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).planned_departure_time as first_center_loading_send_planned_departure_time        -- 始发中心最晚装车发件规划发车时间
--,max(if(step_center = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).planned_arrival_time as first_center_loading_send_planned_arrival_time          -- 始发中心最晚装车发件规划到车时间
--,max(if(step_center = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).actual_departure_time as first_center_loading_send_actual_departure_time         -- 始发中心最晚装车发件实际发车时间
--,max(if(step_center = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).actual_arrival_time as first_center_loading_send_actual_arrival_time           -- 始发中心最晚装车发件实际到车时间
,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).shipment_no) as first_center_loading_send_shipment_no
,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).planned_departure_time) as first_center_loading_send_planned_departure_time
,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).planned_arrival_time,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).planned_arrival_time) as first_center_loading_send_planned_arrival_time
,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).actual_departure_time,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).actual_departure_time) as first_center_loading_send_actual_departure_time
,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).actual_arrival_time,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).actual_arrival_time) as first_center_loading_send_actual_arrival_time
,max(if(step_center = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_code as first_center_loading_send_next_network_code             -- 始发中心最晚装车发件下一站网点编码
,max(if(step_center = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_type as first_center_loading_send_next_network_type             -- 始发中心最晚装车发件下一站网点类型

,max(if(step_center = 2, network_code,null)) as   second_center_network_code        -- 第二转运中心
,min(if(step_center = 2 and scan_type = 'unloading', scan_time_early,null))  as second_center_unloading_early_time       -- 第二中心最早卸车时间
,min(if(step_center = 2 and scan_type = 'unloading', trace_wid,null)).weight  as second_center_unloading_early_weight     -- 第二中心最早卸车扫描对应重量
,min(if(step_center = 2 and scan_type = 'centerArrival', scan_time_early,null)) as second_center_arrival_early_time  -- 第二中心最早到件时间
,min(if(step_center = 2 and scan_type = 'centerArrival', trace_wid,null)).weight as second_center_arrival_early_weight -- 第二中心最早到件扫描对应重量
--,min(if(step_center = 2 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).shipment_no as second_center_unloading_arrival_shipment_no         -- 第二中心最早卸车到件扫描对应任务号
--,min(if(step_center = 2 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).planned_departure_time as second_center_unloading_arrival_planned_departure_time   -- 第二中心最早卸车到件规划发车时间
--,min(if(step_center = 2 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).planned_arrival_time as second_center_unloading_arrival_planned_arrival_time       -- 第二中心最早卸车到件规划到车时间
--,min(if(step_center = 2 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).actual_departure_time as second_center_unloading_arrival_actual_departure_time     -- 第二中心最早卸车到件实际发车时间
--,min(if(step_center = 2 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).actual_arrival_time as second_center_unloading_arrival_actual_arrival_time         -- 第二中心最早卸车到件实际到车时间
,coalesce(min(if(step_center = 2 and  scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_center = 2 and scan_type = 'centerArrival', trace_wid,null)).shipment_no) as second_center_unloading_arrival_shipment_no
,coalesce(min(if(step_center = 2 and  scan_type = 'unloading', trace_wid,null)).planned_departure_time, min(if(step_center = 2 and scan_type = 'centerArrival', trace_wid,null)).planned_departure_time) as second_center_unloading_arrival_planned_departure_time
,coalesce(min(if(step_center = 2 and  scan_type = 'unloading', trace_wid,null)).planned_arrival_time, min(if(step_center = 2 and scan_type = 'centerArrival', trace_wid,null)).planned_arrival_time) as second_center_unloading_arrival_planned_arrival_time
,coalesce(min(if(step_center = 2 and  scan_type = 'unloading', trace_wid,null)).actual_departure_time, min(if(step_center = 2 and scan_type = 'centerArrival', trace_wid,null)).actual_departure_time) as second_center_unloading_arrival_actual_departure_time
,coalesce(min(if(step_center = 2 and  scan_type = 'unloading', trace_wid,null)).actual_arrival_time, min(if(step_center = 2 and scan_type = 'centerArrival', trace_wid,null)).actual_arrival_time) as second_center_unloading_arrival_actual_arrival_time

,max(if(step_center = 2 and scan_type = 'loading', scan_time_last,null)) as second_center_loading_later_time      -- 第二中心最晚装车时间
,max(if(step_center = 2 and scan_type = 'loading', trace_wid,null)).weight as second_center_loading_later_weight  -- 第二中心最晚装车扫描对应重量
,max(if(step_center = 2 and scan_type = 'centerSend' , scan_time_last,null)) as second_center_send_later_time     -- 第二中心最晚发件时间
,max(if(step_center = 2 and scan_type = 'centerSend' , trace_wid,null)).weight as second_center_send_later_weight -- 第二中心最晚发件扫描对应重量
--,max(if(step_center = 2 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).shipment_no as second_center_loading_send_shipment_no                              -- 第二中心最晚装车发件扫描对应任务号
--,max(if(step_center = 2 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).planned_departure_time as second_center_loading_send_planned_departure_time        -- 第二中心最晚装车发件规划发车时间
--,max(if(step_center = 2 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).planned_arrival_time as second_center_loading_send_planned_arrival_time            -- 第二中心最晚装车发件规划到车时间
--,max(if(step_center = 2 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).actual_departure_time as second_center_loading_send_actual_departure_time          -- 第二中心最晚装车发件实际发车时间
--,max(if(step_center = 2 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).actual_arrival_time as second_center_loading_send_actual_arrival_time              -- 第二中心最晚装车发件实际到车时间
,coalesce(max(if(step_center = 2 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_center = 2 and scan_type = 'centerSend', trace_wid,null)).shipment_no) as second_center_loading_send_shipment_no
,coalesce(max(if(step_center = 2 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_center = 2 and scan_type = 'centerSend', trace_wid,null)).planned_departure_time) as second_center_loading_send_planned_departure_time
,coalesce(max(if(step_center = 2 and scan_type = 'loading', trace_wid,null)).planned_arrival_time,max(if(step_center = 2 and scan_type = 'centerSend', trace_wid,null)).planned_arrival_time) as second_center_loading_send_planned_arrival_time
,coalesce(max(if(step_center = 2 and scan_type = 'loading', trace_wid,null)).actual_departure_time,max(if(step_center = 2 and scan_type = 'centerSend', trace_wid,null)).actual_departure_time) as second_center_loading_send_actual_departure_time
,coalesce(max(if(step_center = 2 and scan_type = 'loading', trace_wid,null)).actual_arrival_time,max(if(step_center = 2 and scan_type = 'centerSend', trace_wid,null)).actual_arrival_time) as second_center_loading_send_actual_arrival_time


,max(if(step_center = 2 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_code as second_center_loading_send_next_network_code                  -- 第二中心最晚装车发件下一站网点编码
,max(if(step_center = 2 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_type as second_center_loading_send_next_network_type                  -- 第二中心最晚装车发件下一站网点类型

,max(if(step_center = 3, network_code,null)) as third_center_network_code             -- 第三转运中心
,min(if(step_center = 3 and scan_type = 'unloading', scan_time_early,null))  as third_center_unloading_early_time       -- 第三中心最早卸车时间
,min(if(step_center = 3 and scan_type = 'unloading', trace_wid,null)).weight  as third_center_unloading_early_weight     -- 第三中心最早卸车扫描对应重量
,min(if(step_center = 3 and scan_type = 'centerArrival', scan_time_early,null)) as thirdcenter_arrival_early_time     -- 第三中心最早到件时间
,min(if(step_center = 3 and scan_type = 'centerArrival', trace_wid,null)).weight as third_center_arrival_early_weight -- 第三中心最早到件扫描对应重量
--,min(if(step_center = 3 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).shipment_no as third_center_unloading_arrival_shipment_no                    -- 第三中心最早卸车到件扫描对应任务号
--,min(if(step_center = 3 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).planned_departure_time as third_center_unloading_arrival_planned_departure_time   -- 第三中心最早卸车到件规划发车时间
--,min(if(step_center = 3 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).planned_arrival_time as third_center_unloading_arrival_planned_arrival_time       -- 第三中心最早卸车到件规划到车时间
--,min(if(step_center = 3 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).actual_departure_time as third_center_unloading_arrival_actual_departure_time     -- 第三中心最早卸车到件实际发车时间
--,min(if(step_center = 3 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).actual_arrival_time as third_center_unloading_arrival_actual_arrival_time         -- 第三中心最早卸车到件实际到车时间
,coalesce(min(if(step_center = 3 and  scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_center = 3 and scan_type = 'centerArrival', trace_wid,null)).shipment_no) as third_center_unloading_arrival_shipment_no
,coalesce(min(if(step_center = 3 and  scan_type = 'unloading', trace_wid,null)).planned_departure_time, min(if(step_center = 3 and scan_type = 'centerArrival', trace_wid,null)).planned_departure_time) as third_center_unloading_arrival_planned_departure_time
,coalesce(min(if(step_center = 3 and  scan_type = 'unloading', trace_wid,null)).planned_arrival_time, min(if(step_center = 3 and scan_type = 'centerArrival', trace_wid,null)).planned_arrival_time) as third_center_unloading_arrival_planned_arrival_time
,coalesce(min(if(step_center = 3 and  scan_type = 'unloading', trace_wid,null)).actual_departure_time, min(if(step_center = 3 and scan_type = 'centerArrival', trace_wid,null)).actual_departure_time) as third_center_unloading_arrival_actual_departure_time
,coalesce(min(if(step_center = 3 and  scan_type = 'unloading', trace_wid,null)).actual_arrival_time, min(if(step_center = 3 and scan_type = 'centerArrival', trace_wid,null)).actual_arrival_time) as third_center_unloading_arrival_actual_arrival_time


,max(if(step_center = 3 and scan_type = 'loading', scan_time_last,null)) as third_center_loading_later_time      -- 第三中心最晚装车时间
,max(if(step_center = 3 and scan_type = 'loading', trace_wid,null)).weight as third_center_loading_later_weight  -- 第三中心最晚装车扫描对应重量
,max(if(step_center = 3 and scan_type = 'centerSend', scan_time_last,null)) as third_center_send_later_time     -- 第三中心最晚发件时间
,max(if(step_center = 3 and scan_type = 'centerSend', trace_wid,null)).weight as third_center_send_later_weight -- 第三中心最晚发件扫描对应重量
--,max(if(step_center = 3 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).shipment_no as third_center_loading_send_shipment_no                              -- 第三中心最晚装车发件扫描对应任务号
--,max(if(step_center = 3 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).planned_departure_time as third_center_loading_send_planned_departure_time        -- 第三中心最晚装车发件规划发车时间
--,max(if(step_center = 3 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).planned_arrival_time as third_center_loading_send_planned_arrival_time            -- 第三中心最晚装车发件规划到车时间
--,max(if(step_center = 3 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).actual_departure_time as third_center_loading_send_actual_departure_time          -- 第三中心最晚装车发件实际发车时间
--,max(if(step_center = 3 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).actual_arrival_time as third_center_loading_send_actual_arrival_time              -- 第三中心最晚装车发件实际到车时间
,coalesce(max(if(step_center = 3 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_center = 3 and scan_type = 'centerSend', trace_wid,null)).shipment_no) as third_center_loading_send_shipment_no
,coalesce(max(if(step_center = 3 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_center = 3 and scan_type = 'centerSend', trace_wid,null)).planned_departure_time) as third_center_loading_send_planned_departure_time
,coalesce(max(if(step_center = 3 and scan_type = 'loading', trace_wid,null)).planned_arrival_time,max(if(step_center = 3 and scan_type = 'centerSend', trace_wid,null)).planned_arrival_time) as third_center_loading_send_planned_arrival_time
,coalesce(max(if(step_center = 3 and scan_type = 'loading', trace_wid,null)).actual_departure_time,max(if(step_center = 3 and scan_type = 'centerSend', trace_wid,null)).actual_departure_time) as third_center_loading_send_actual_departure_time
,coalesce(max(if(step_center = 3 and scan_type = 'loading', trace_wid,null)).actual_arrival_time,max(if(step_center = 3 and scan_type = 'centerSend', trace_wid,null)).actual_arrival_time) as third_center_loading_send_actual_arrival_time

,max(if(step_center = 3 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_code as third_center_loading_send_next_network_code                  -- 第三中心最晚装车发件下一站网点编码
,max(if(step_center = 3 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_type as third_center_loading_send_next_network_type                  -- 第三中心最晚装车发件下一站网点类型

,max(if(step_center = 4, network_code,null)) as fourth_center_network_code             -- 第四转运中心
,min(if(step_center = 4 and scan_type = 'unloading', scan_time_early,null))  as fourth_center_unloading_early_time       -- 第四中心最早卸车时间
,min(if(step_center = 4 and scan_type = 'unloading', trace_wid,null)).weight  as fourth_center_unloading_early_weight     -- 第四中心最早卸车扫描对应重量
,min(if(step_center = 4 and scan_type = 'centerArrival', scan_time_early,null)) as fourth_center_arrival_early_time    -- 第四中心最早到件时间
,min(if(step_center = 4 and scan_type = 'centerArrival', trace_wid,null)).weight as fourth_center_arrival_early_weight -- 第四中心最早到件扫描对应重量
--,min(if(step_center = 4 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).shipment_no as fourth_center_unloading_arrival_shipment_no                    -- 第四中心最早卸车到件扫描对应任务号
--,min(if(step_center = 4 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).planned_departure_time as fourth_center_unloading_arrival_planned_departure_time   -- 第四中心最早卸车到件规划发车时间
--,min(if(step_center = 4 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).planned_arrival_time as fourth_center_unloading_arrival_planned_arrival_time       -- 第四中心最早卸车到件规划到车时间
--,min(if(step_center = 4 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).actual_departure_time as fourth_center_unloading_arrival_actual_departure_time     -- 第四中心最早卸车到件实际发车时间
--,min(if(step_center = 4 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).actual_arrival_time as fourth_center_unloading_arrival_actual_arrival_time         -- 第四中心最早卸车到件实际到车时间
,coalesce(min(if(step_center = 4 and  scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_center = 4 and scan_type = 'centerArrival', trace_wid,null)).shipment_no) as fourth_center_unloading_arrival_shipment_no
,coalesce(min(if(step_center = 4 and  scan_type = 'unloading', trace_wid,null)).planned_departure_time, min(if(step_center = 4 and scan_type = 'centerArrival', trace_wid,null)).planned_departure_time) as fourth_center_unloading_arrival_planned_departure_time
,coalesce(min(if(step_center = 4 and  scan_type = 'unloading', trace_wid,null)).planned_arrival_time, min(if(step_center = 4 and scan_type = 'centerArrival', trace_wid,null)).planned_arrival_time) as fourth_center_unloading_arrival_planned_arrival_time
,coalesce(min(if(step_center = 4 and  scan_type = 'unloading', trace_wid,null)).actual_departure_time, min(if(step_center = 4 and scan_type = 'centerArrival', trace_wid,null)).actual_departure_time) as fourth_center_unloading_arrival_actual_departure_time
,coalesce(min(if(step_center = 4 and  scan_type = 'unloading', trace_wid,null)).actual_arrival_time, min(if(step_center = 4 and scan_type = 'centerArrival', trace_wid,null)).actual_arrival_time) as fourth_center_unloading_arrival_actual_arrival_time


,max(if(step_center = 4 and scan_type = 'loading', scan_time_last,null)) as fourth_center_loading_later_time                                 -- 第四中心最晚装车时间
,max(if(step_center = 4 and scan_type = 'loading', trace_wid,null)).weight as fourth_center_loading_later_weight                             -- 第四中心最晚装车扫描对应重量
,max(if(step_center = 4 and scan_type = 'centerSend', scan_time_last,null)) as fourth_center_send_later_time                                 -- 第四中心最晚发件时间
,max(if(step_center = 4 and scan_type = 'centerSend', trace_wid,null)).weight as fourth_center_send_later_weight                             -- 第四中心最晚发件扫描对应重量
--,max(if(step_center = 4 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).shipment_no as fourth_center_loading_send_shipment_no                              -- 第四中心最晚装车发件扫描对应任务号
--,max(if(step_center = 4 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).planned_departure_time as fourth_center_loading_send_planned_departure_time        -- 第四中心最晚装车发件规划发车时间
--,max(if(step_center = 4 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).planned_arrival_time as fourth_center_loading_send_planned_arrival_time            -- 第四中心最晚装车发件规划到车时间
--,max(if(step_center = 4 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).actual_departure_time as fourth_center_loading_send_actual_departure_time          -- 第四中心最晚装车发件实际发车时间
--,max(if(step_center = 4 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).actual_arrival_time as fourth_center_loading_send_actual_arrival_time              -- 第四中心最晚装车发件实际到车时间
,coalesce(max(if(step_center = 4 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_center = 4 and scan_type = 'centerSend', trace_wid,null)).shipment_no) as fourth_center_loading_send_shipment_no
,coalesce(max(if(step_center = 4 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_center = 4 and scan_type = 'centerSend', trace_wid,null)).planned_departure_time) as fourth_center_loading_send_planned_departure_time
,coalesce(max(if(step_center = 4 and scan_type = 'loading', trace_wid,null)).planned_arrival_time,max(if(step_center = 4 and scan_type = 'centerSend', trace_wid,null)).planned_arrival_time) as fourth_center_loading_send_planned_arrival_time
,coalesce(max(if(step_center = 4 and scan_type = 'loading', trace_wid,null)).actual_departure_time,max(if(step_center = 4 and scan_type = 'centerSend', trace_wid,null)).actual_departure_time) as fourth_center_loading_send_actual_departure_time
,coalesce(max(if(step_center = 4 and scan_type = 'loading', trace_wid,null)).actual_arrival_time,max(if(step_center = 4 and scan_type = 'centerSend', trace_wid,null)).actual_arrival_time) as fourth_center_loading_send_actual_arrival_time
,max(if(step_center = 4 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_code as fourth_center_loading_send_next_network_code                  -- 第四中心最晚装车发件下一站网点编码
,max(if(step_center = 4 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_type as fourth_center_loading_send_next_network_type                  -- 第四中心最晚装车发件下一站网点类型

,max(if(step_center_reserve = 1, network_code,null)) as  end_center_network_code                                        -- 末端转运中心
,min(if(step_center_reserve = 1 and scan_type = 'unloading', scan_time_early,null))  as end_center_unloading_early_time                                  -- 末端中心最早卸车时间
,min(if(step_center_reserve = 1 and scan_type = 'unloading', trace_wid,null)).weight  as end_center_unloading_early_weight                                -- 末端中心最早卸车扫描对应重量
,min(if(step_center_reserve = 1 and scan_type = 'centerArrival', scan_time_early,null)) as end_center_arrival_early_time    -- 末端中心最早到件时间
,min(if(step_center_reserve = 1 and scan_type = 'centerArrival', trace_wid,null)).weight as end_center_arrival_early_weight -- 末端中心最早到件扫描对应重量
--,min(if(step_center_reserve = 1 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).shipment_no as end_center_unloading_arrival_shipment_no                    -- 末端中心最早卸车到件扫描对应任务号
--,min(if(step_center_reserve = 1 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).planned_departure_time as end_center_unloading_arrival_planned_departure_time   -- 末端中心最早卸车到件规划发车时间
--,min(if(step_center_reserve = 1 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).planned_arrival_time as end_center_unloading_arrival_planned_arrival_time       -- 末端中心最早卸车到件规划到车时间
--,min(if(step_center_reserve = 1 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).actual_departure_time as end_center_unloading_arrival_actual_departure_time     -- 末端中心最早卸车到件实际发车时间
--,min(if(step_center_reserve = 1 and (scan_type = 'unloading' or scan_type = 'centerArrival'), trace_wid,null)).actual_arrival_time as end_center_unloading_arrival_actual_arrival_time         -- 末端中心最早卸车到件实际到车时间
,coalesce(min(if(step_center_reserve = 1 and  scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_center_reserve = 1 and scan_type = 'centerArrival', trace_wid,null)).shipment_no) as end_center_unloading_arrival_shipment_no
,coalesce(min(if(step_center_reserve = 1 and  scan_type = 'unloading', trace_wid,null)).planned_departure_time, min(if(step_center_reserve = 1 and scan_type = 'centerArrival', trace_wid,null)).planned_departure_time) as end_center_unloading_arrival_planned_departure_time
,coalesce(min(if(step_center_reserve = 1 and  scan_type = 'unloading', trace_wid,null)).planned_arrival_time, min(if(step_center_reserve = 1 and scan_type = 'centerArrival', trace_wid,null)).planned_arrival_time) as end_center_unloading_arrival_planned_arrival_time
,coalesce(min(if(step_center_reserve = 1 and  scan_type = 'unloading', trace_wid,null)).actual_departure_time, min(if(step_center_reserve = 1 and scan_type = 'centerArrival', trace_wid,null)).actual_departure_time) as end_center_unloading_arrival_actual_departure_time
,coalesce(min(if(step_center_reserve = 1 and  scan_type = 'unloading', trace_wid,null)).actual_arrival_time, min(if(step_center_reserve = 1 and scan_type = 'centerArrival', trace_wid,null)).actual_arrival_time) as end_center_unloading_arrival_actual_arrival_time


,max(if(step_center_reserve = 1 and scan_type = 'loading', scan_time_last,null)) as end_center_loading_later_time                                 -- 末端中心最晚装车时间
,max(if(step_center_reserve = 1 and scan_type = 'loading', trace_wid,null)).weight as end_center_loading_later_weight                             -- 末端中心最晚装车扫描对应重量
,max(if(step_center_reserve = 1 and scan_type = 'centerSend', scan_time_last,null)) as end_center_send_later_time     -- 末端中心最晚发件时间
,max(if(step_center_reserve = 1 and scan_type = 'centerSend', trace_wid,null)).weight as end_center_send_later_weight -- 末端中心最晚发件扫描对应重量
--,max(if(step_center_reserve = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).shipment_no as end_center_loading_send_shipment_no                         -- 末端中心最晚装车发件扫描对应任务号
--,max(if(step_center_reserve = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).planned_departure_time as end_center_loading_send_planned_departure_time   -- 末端中心最晚装车发件规划发车时间
--,max(if(step_center_reserve = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).planned_arrival_time as end_center_loading_send_planned_arrival_time       -- 末端中心最晚装车发件规划到车时间
--,max(if(step_center_reserve = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).actual_departure_time as end_center_loading_send_actual_departure_time     -- 末端中心最晚装车发件实际发车时间
--,max(if(step_center_reserve = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).actual_arrival_time as end_center_loading_send_actual_arrival_time         -- 末端中心最晚装车发件实际到车时间
,coalesce(max(if(step_center_reserve = 1 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_center_reserve = 1 and scan_type = 'centerSend', trace_wid,null)).shipment_no) as end_center_loading_send_shipment_no
,coalesce(max(if(step_center_reserve = 1 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_center_reserve = 1 and scan_type = 'centerSend', trace_wid,null)).planned_departure_time) as end_center_loading_send_planned_departure_time
,coalesce(max(if(step_center_reserve = 1 and scan_type = 'loading', trace_wid,null)).planned_arrival_time,max(if(step_center_reserve = 1 and scan_type = 'centerSend', trace_wid,null)).planned_arrival_time) as end_center_loading_send_planned_arrival_time
,coalesce(max(if(step_center_reserve = 1 and scan_type = 'loading', trace_wid,null)).actual_departure_time,max(if(step_center_reserve = 1 and scan_type = 'centerSend', trace_wid,null)).actual_departure_time) as end_center_loading_send_actual_departure_time
,coalesce(max(if(step_center_reserve = 1 and scan_type = 'loading', trace_wid,null)).actual_arrival_time,max(if(step_center_reserve = 1 and scan_type = 'centerSend', trace_wid,null)).actual_arrival_time) as end_center_loading_send_actual_arrival_time

,max(if(step_center_reserve = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_code as end_center_loading_send_next_network_code             -- 末端中心最晚装车发件下一站网点编码
,max(if(step_center_reserve = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_type as end_center_loading_send_next_network_type             -- 末端中心最晚装车发件下一站网点类型

,min(if(step_entrepot_reserve = 1, network_code,null)) as end_nodal_network_code                                                                              -- 末端集散网点
,min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', scan_time_last,null))   as end_nodal_unloading_early_time                                      -- 末端集散最早卸车时间
,min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', trace_wid,null)).weight   as end_nodal_unloading_early_weight                                  -- 末端集散最早卸车扫描对应重量
,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', scan_time_early,null))    as end_nodal_arrival_early_time    -- 末端集散最早到件时间
,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', trace_wid,null)).weight   as end_nodal_arrival_early_weight  -- 末端集散最早到件扫描对应重量
--,min(if(step_entrepot_reserve = 1 and (scan_type = 'unloading' or scan_type = 'siteArrival'), trace_wid,null)).shipment_no             as end_nodal_unloading_arrival_shipment_no              -- 末端集散最早卸车到件扫描对应任务号
--,min(if(step_entrepot_reserve = 1 and (scan_type = 'unloading' or scan_type = 'siteArrival'), trace_wid,null)).planned_departure_time  as end_nodal_unloading_arrival_planned_departure_time   -- 末端集散最早卸车到件规划发车时间
--,min(if(step_entrepot_reserve = 1 and (scan_type = 'unloading' or scan_type = 'siteArrival'), trace_wid,null)).planned_arrival_time    as end_nodal_unloading_arrival_planned_arrival_time     -- 末端集散最早卸车到件规划到车时间
--,min(if(step_entrepot_reserve = 1 and (scan_type = 'unloading' or scan_type = 'siteArrival'), trace_wid,null)).actual_departure_time   as end_nodal_unloading_arrival_actual_departure_time    -- 末端集散最早卸车到件实际发车时间
--,min(if(step_entrepot_reserve = 1 and (scan_type = 'unloading' or scan_type = 'siteArrival'), trace_wid,null)).actual_arrival_time     as end_nodal_unloading_arrival_actual_arrival_time      -- 末端集散最早卸车到件实际到车时间
,coalesce(min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', trace_wid,null)).shipment_no) as end_nodal_unloading_arrival_shipment_no
,coalesce(min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', trace_wid,null)).planned_departure_time,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', trace_wid,null)).planned_departure_time) as end_nodal_unloading_arrival_planned_departure_time
,coalesce(min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', trace_wid,null)).planned_arrival_time,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', trace_wid,null)).planned_arrival_time) as end_nodal_unloading_arrival_planned_arrival_time
,coalesce(min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', trace_wid,null)).actual_departure_time,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', trace_wid,null)).actual_departure_time) as end_nodal_unloading_arrival_actual_departure_time
,coalesce(min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', trace_wid,null)).actual_arrival_time,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', trace_wid,null)).actual_arrival_time) as end_nodal_unloading_arrival_actual_arrival_time


,max(if(step_entrepot_reserve = 1 and scan_type = 'loading', scan_time_last,null))   as end_nodal_loading_later_time                                 -- 末端集散最晚装车时间
,max(if(step_entrepot_reserve = 1 and scan_type = 'loading', trace_wid,null)).weight as end_nodal_loading_later_weight                               -- 末端集散最晚装车扫描对应重量
,max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend' , scan_time_last,null))   as end_nodal_send_later_time     -- 末端集散最晚发车时间
,max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend' , trace_wid,null)).weight as end_nodal_send_later_weight   -- 末端集散最晚发车扫描对应重量
--,max(if(step_entrepot_reserve = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).shipment_no as end_nodal_loading_send_shipment_no                        -- 末端集散最晚装车发件扫描对应任务号
--,max(if(step_entrepot_reserve = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).planned_departure_time as end_nodal_loading_send_planned_departure_time  -- 末端集散最晚装车发件规划发车时间
--,max(if(step_entrepot_reserve = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).planned_arrival_time as end_nodal_loading_send_planned_arrival_time      -- 末端集散最晚装车发件规划到车时间
--,max(if(step_entrepot_reserve = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).actual_departure_time as end_nodal_loading_send_actual_departure_time    -- 末端集散最晚装车发件实际发车时间
--,max(if(step_entrepot_reserve = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).actual_arrival_time as end_nodal_loading_send_actual_arrival_time        -- 末端集散最晚装车发件实际到车时间
,coalesce(max(if(step_entrepot_reserve = 1 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend', trace_wid,null)).shipment_no) as end_nodal_loading_send_shipment_no
,coalesce(max(if(step_entrepot_reserve = 1 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend', trace_wid,null)).planned_departure_time) as end_nodal_loading_send_planned_departure_time
,coalesce(max(if(step_entrepot_reserve = 1 and scan_type = 'loading', trace_wid,null)).planned_arrival_time, max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend', trace_wid,null)).planned_arrival_time) as end_nodal_loading_send_planned_arrival_time
,coalesce(max(if(step_entrepot_reserve = 1 and scan_type = 'loading', trace_wid,null)).actual_departure_time, max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend', trace_wid,null)).actual_departure_time) as end_nodal_loading_send_actual_departure_time
,coalesce(max(if(step_entrepot_reserve = 1 and scan_type = 'loading', trace_wid,null)).actual_arrival_time, max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend', trace_wid,null)).actual_arrival_time) as end_nodal_loading_send_actual_arrival_time

,max(if(step_entrepot_reserve = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).next_network_code as end_nodal_loading_send_next_network_code            -- 末端集散最晚装车发件下一站网点编码
,max(if(step_entrepot_reserve = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).next_network_type as end_nodal_loading_send_next_network_type            -- 末端集散最晚装车发件下一站网点类型

,max(trace_wid).network_code as end_network_network_code                                                                                   --末端网点网点编码
,max(if(scan_type = 'unloading', scan_time_early, null)) as end_network_unloading_early_time                                               --末端网点最最早卸车时间
,max(if(scan_type = 'siteArrival' or scan_type = 'centerArrival', scan_time_early, null)) as end_network_arrival_early_time                 --末端网点最最早到件时间
,min(if(scan_type = 'deliver',scan_time_early,null)) as deliver_early_scantime                                                             --最早派件时间
,max(if(scan_type = 'deliver',scan_time_last,null)) as deliver_later_scantime                                                              --最晚派件时间
,max(if(scan_type = 'deliver',trace_wid,null)).network_code as deliver_later_scansitecode                                                  --最晚派件网点
,max(if(scan_type = 'deliver',trace_wid,null)).weight as deliver_later_weight                                                              --最晚派件重量
,max(if(scan_type = 'deliver',trace_wid,null)).scanuser as deliver_later_user                                                              --最晚派件人
,max(if(scan_type = 'sign',scan_time_last,null)) as sign_later_scantime                                                                    --最晚签收时间
,min(if(scan_type = 'sign',scan_time_early,null)) as sign_early_scantime                                                                   --最早签收时间
,min(if(scan_type = 'sign',trace_wid,null)).network_code as sign_early_scansitecode                                                        --最早签收网点
,min(if(scan_type = 'sign',trace_wid,null)).weight as sign_early_weight                                                                    --最早签收重量
,min(if(scan_type = 'sign' or scan_type = 'outStore' or scan_type = 'inStore',scan_time_early,null)) as aging_sign_scantime                --实效签收时间
,min(if(scan_type = 'sign' or scan_type = 'outStore' or scan_type = 'inStore',trace_wid,null)).network_code as aging_sign_scansitecode     --实效签收网点
,min(if(scan_type = 'sign' or scan_type = 'outStore' or scan_type = 'inStore',trace_wid,null)).weight as aging_sign_weight                 --实效签收重量
,min(if(scan_type = 'sign' or scan_type = 'outStore' or scan_type = 'inStore',trace_wid,null)).scan_type as aging_sign_scantype            --实效签收扫描类型
,min(if(scan_type = 'sign' or scan_type = 'outStore',scan_time_early,null)) as actual_sign_scantime                                        --实际签收时间
,min(if(scan_type = 'sign' or scan_type = 'outStore',trace_wid,null)).network_code as actual_sign_scansitecode                             --实际签收网点
,min(if(scan_type = 'sign' or scan_type = 'outStore',trace_wid,null)).weight as actual_sign_weight                                         --实际签收重量
,min(if(scan_type = 'sign' or scan_type = 'outStore',trace_wid,null)).scan_type as actual_sign_scantype                                    --实际签收扫描类型
,date_format(max(scan_time_last),'yyyy-MM-dd') as dt
from operations_trace_basic group by waybill_no
)

insert overwrite table jms_dwd.dwd_s02_whole_operations_waybill_trace_mid_dt partition(dt) select * from waybill_and_step distribute by dt, abs(hash(waybill_and_step.waybill_no)) % 120 ;